﻿create PROCEDURE Sync.SP_BoardLoans_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @BoardLoans_Temp TABLE 
		(
			BoardID UniqueIdentifier,
	LoanID UniqueIdentifier,
			[EnteringNotes] nvarchar(500),
				[BoardLoanAmount] float,
				[BoardPaymentPeriod] int,
				[BoardpaymentMethodID] uniqueidentifier,
				[StatusNotes] nvarchar(MAX),
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @BoardLoans_Temp 
	   SELECT	Tbl.Col.value('BoardID[1]','uniqueidentifier') BoardID,
Tbl.Col.value('LoanID[1]','uniqueidentifier') LoanID,
                Tbl.Col.value('EnteringNotes[1]', 'nvarchar(500)') [EnteringNotes],
Tbl.Col.value('BoardLoanAmount[1]', 'float') [BoardLoanAmount],
Tbl.Col.value('BoardPaymentPeriod[1]', 'int') [BoardPaymentPeriod],
Tbl.Col.value('BoardpaymentMethodID[1]', 'uniqueidentifier') [BoardpaymentMethodID],
Tbl.Col.value('StatusNotes[1]', 'nvarchar(MAX)') [StatusNotes],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/BoardLoans') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[BoardLoans] ([BoardID],[LoanID],[EnteringNotes],[BoardLoanAmount],[BoardPaymentPeriod],[BoardpaymentMethodID],[StatusNotes])
	SELECT t.[BoardID],t.[LoanID],t.[EnteringNotes],t.[BoardLoanAmount],t.[BoardPaymentPeriod],t.[BoardpaymentMethodID],t.[StatusNotes]
	FROM  @BoardLoans_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.BoardLoans_Tracking s
        WHERE t.BoardID = s.BoardID AND t.LoanID = s.LoanID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[BoardLoans]
	SET [BoardLoans].[EnteringNotes] = t.[EnteringNotes],[BoardLoans].[BoardLoanAmount] = t.[BoardLoanAmount],[BoardLoans].[BoardPaymentPeriod] = t.[BoardPaymentPeriod],[BoardLoans].[BoardpaymentMethodID] = t.[BoardpaymentMethodID],[BoardLoans].[StatusNotes] = t.[StatusNotes] 
	FROM [dbo].[BoardLoans] s JOIN @BoardLoans_Temp t 
    ON t.BoardID = s.BoardID AND t.LoanID = s.LoanID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.BoardLoans_Tracking r
				  WHERE t.BoardID = r.BoardID AND t.LoanID = r.LoanID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[BoardLoans]
    FROM @BoardLoans_Temp t JOIN [dbo].[BoardLoans] s
    ON t.BoardID = s.BoardID AND t.LoanID = s.LoanID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END











